namespace CISLib
{
    using System;
    using System.Data;
    using System.Data.SqlClient;

    public class ManageCustomerDef
    {
        public static SqlDataReader GetCustomerDef()
        {
            string strSQL = "Select * from TB_CustomerDefine order by DefName ";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static SqlDataReader GetCustomerDef(Guid areaId)
        {
            string strSQL = "Select * from TB_CustomerDefine where AreaId=@AreaId order by DefName ";
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            return MyDB.DBReader;
        }

        public static CustomerDef GetDefViaId(Guid id)
        {
            SqlDataReader MyReader = null;
            CustomerDef customerDef = new CustomerDef();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            string strSQL = "select * from TB_CustomerDefine where DefId = @DefId";
            MyDB.AddParameters(id, "DefId", SqlDbType.UniqueIdentifier);
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteReader();
            MyReader = MyDB.DBReader;
            if (MyReader.Read())
            {
                customerDef.DefId = MyReader.GetGuid(0);
                customerDef.DefName = MyReader.GetString(1);
                customerDef.DefNameEn = MyReader.GetString(2);
                customerDef.AreaId = MyReader.GetGuid(3);
            }
            MyDB.DBClose();
            return customerDef;
        }

        public static void InsertNewDef(string name, string nameEn, Guid areaId)
        {
            Guid id = Guid.NewGuid();
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "DefName", SqlDbType.VarChar);
            MyDB.AddParameters(nameEn, "DefNameEn", SqlDbType.VarChar);
            MyDB.AddParameters(id, "DefId", SqlDbType.UniqueIdentifier);
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            string strSQL = "Insert into TB_CustomerDefine (DefName,DefNameEn,DefId,AreaId) values (@DefName,@DefNameEn,@DefId,@AreaId)";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static bool IsExistDefInOtherTable(Guid defId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(defId, "CustomerDefId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_CustomerBasicInfo where CustomerDefId = @CustomerDefId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
            }
            else
            {
                MyDB.DBClose();
                return true;
            }
            return false;
        }

        public static bool IsExistSameDefName(string name, Guid areaId)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "DefName", SqlDbType.VarChar);
            MyDB.AddParameters(areaId, "AreaId", SqlDbType.UniqueIdentifier);
            string strSQL = "select * from TB_CustomerDefine where DefName = @DefName and AreaId=@AreaId";
            MyDB.DBSetSelect(strSQL);
            if (MyDB.DBExecuteScalar() == null)
            {
                MyDB.DBClose();
                return false;
            }
            MyDB.DBClose();
            return true;
        }

        public static void RemoveDefViaId(Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(id, "DefId", SqlDbType.UniqueIdentifier);
            string strSQL = "Delete From TB_CustomerDefine where DefId = @DefId and DefId <> '11111111-1111-1111-1111-111111111111'";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }

        public static void UpdateDef(string name, string nameEn, Guid id)
        {
            DBAccess MyDB = new DBAccess();
            MyDB.DBOpen();
            MyDB.AddParameters(name, "DefName", SqlDbType.VarChar);
            MyDB.AddParameters(nameEn, "DefNameEn", SqlDbType.VarChar);
            MyDB.AddParameters(id, "DefId", SqlDbType.UniqueIdentifier);
            string strSQL = "Update TB_CustomerDefine set DefName = @DefName,DefNameEn=@DefNameEn where DefId = @DefId and DefId <> '11111111-1111-1111-1111-111111111111'";
            MyDB.DBSetSelect(strSQL);
            MyDB.DBExcuteNonQuery();
            MyDB.DBClose();
        }
    }
}

